package database.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import database.bean.WebcamBEAN;
import database.manager.DatabaseManager;

public class WebcamDAO {
	private DatabaseManager		dbm			= null;

	private PreparedStatement	ppstm		= null;

	private String				addVideo	= "INSERT INTO Webcam( dateTime, videoName ) VALUES(?, ?)";
	private String				allVideos	= "SELECT * FROM Webcam ";
	private String				renameVideo	= "UPDATE Webcam SET videoName = ? WHERE videoName = ?";
	private String				delVideo	= "DELETE from Webcam WHERE videoName = ?";
	private static String		newEntryId	= "SELECT COUNT(*) AS newid FROM Webcam";

	public WebcamDAO( DatabaseManager dbm ) {
		this.dbm = dbm;
	}

	public void addVideo( String name ) {
		try {
			ppstm = dbm.prepareStatement( addVideo );
			ppstm.setString( 1, getStringFromCalendar( Calendar.getInstance().getTime() ) );
			ppstm.setString( 2, name );
			ppstm.execute();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
	}

	public ArrayList<WebcamBEAN> getAllVideos() {
		ArrayList<WebcamBEAN> list = new ArrayList<WebcamBEAN>();
		try {
			ppstm = dbm.prepareStatement( allVideos );
			ResultSet res = ppstm.executeQuery();
			while( res.next() ) {
				list.add( new WebcamBEAN( res.getInt( "videoId" ), getCalendarFromString( res.getString( "dateTime" ) ), res.getString( "videoName" ) ) );
			}
			res.close();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return list;
	}

	// this should only be used in the IPC server section!
	public void deleteVideo( String name ) {
		try {
			ppstm = dbm.prepareStatement( delVideo );
			ppstm.setString( 1, name );
			ppstm.execute();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
	}

	public int getNewEntry() {
		int newID = 0;
		try {
			PreparedStatement ppstm = dbm.prepareStatement( newEntryId );
			ResultSet res = ppstm.executeQuery();

			if( res.next() ) {
				newID = res.getInt( "newid" );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return newID + 1;
	}

	public void renameVideo( String oldName, String newName ) {
		try {
			ppstm = dbm.prepareStatement( renameVideo );
			ppstm.setString( 1, newName );
			ppstm.setString( 2, oldName );
			ppstm.execute();
		} catch( SQLException e ) {
			e.printStackTrace();
		}
	}

	private String getStringFromCalendar( Date time ) {
		if( time == null )
			return "";
		SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
		return sdf.format( time.getTime() );
	}

	/**
	 * 
	 * @param time
	 *            format = yyyy-MM-dd HH:mm:ss
	 * @return
	 */
	private Calendar getCalendarFromString( String time ) {
		if( time == null )
			return null;
		SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
		Date date = null;
		try {
			date = sdf.parse( time );
		} catch( ParseException e ) {
			e.printStackTrace();
		}
		Calendar cal = new GregorianCalendar();
		cal.setTime( date );
		return cal;
	}
}
